CREATE TABLE TB_USER(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,
  login_name VARCHAR2(15) NOT NULL,
  password VARCHAR(15) NOT NULL,
  active CHAR(1) NOT NULL,
  CONSTRAINT USER_id_pk PRIMARY KEY (id)
);

ALTER TABLE TB_USER ADD CONSTRAINT USER_login_unique UNIQUE(LOGIN_NAME);

CREATE TABLE TB_RECORDER(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,  
  CONSTRAINT RECORDER_id_pk PRIMARY KEY (id)
);

CREATE TABLE TB_GENRE(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,
  CONSTRAINT GENRE_id_pk PRIMARY KEY (id)
);

CREATE TABLE TB_CITY(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,
  state_name VARCHAR2(50) NOT NULL,
  country_name VARCHAR(15) NOT NULL,
  CONSTRAINT CITY_id_pk PRIMARY KEY (id)
);

CREATE TABLE TB_ARTIST(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,
  artist_year NUMBER,
  history VARCHAR2(600),
  id_origin_city NUMBER NOT NULL,
  id_genre NUMBER NOT NULL,
  id_updater NUMBER NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT ARTIST_id_pk PRIMARY KEY (id)
);


ALTER TABLE TB_ARTIST ADD CONSTRAINT ARTIST_id_origin_city_fk FOREIGN KEY (id_origin_city) REFERENCES TB_CITY (id);
ALTER TABLE TB_ARTIST ADD CONSTRAINT ARTIST_id_genre_fk FOREIGN KEY (id_genre) REFERENCES TB_GENRE (id);
ALTER TABLE TB_ARTIST ADD CONSTRAINT ARTIST_id_updater_fk FOREIGN KEY (id_updater) REFERENCES TB_USER (id);

CREATE TABLE TB_MUSICIAN(
  id NUMBER NOT NULL,
  name VARCHAR2(40) NOT NULL,
  birthday DATE NOT NULL,
  gender CHAR(1) NOT NULL,
  biography VARCHAR2(600),
  id_origin_city NUMBER NOT NULL,
  id_updater NUMBER NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT MUSICIAN_id_pk PRIMARY KEY (id)
);

ALTER TABLE TB_MUSICIAN ADD CONSTRAINT MUSICIAN_id_origin_city_fk FOREIGN KEY (id_origin_city) REFERENCES TB_CITY (id);
ALTER TABLE TB_MUSICIAN ADD CONSTRAINT MUSICIAN_id_updater_fk FOREIGN KEY (id_updater) REFERENCES TB_USER (id);

CREATE TABLE TB_ARTIST_MUSICIAN(
  id_artist NUMBER NOT NULL,
  id_musician NUMBER NOT NULL
);

ALTER TABLE TB_ARTIST_MUSICIAN ADD CONSTRAINT ARTMUS_id_artist_fk FOREIGN KEY (id_artist) REFERENCES TB_ARTIST (id);
ALTER TABLE TB_ARTIST_MUSICIAN ADD CONSTRAINT ARTMUS_id_musician_fk FOREIGN KEY (id_musician) REFERENCES TB_MUSICIAN (id);


CREATE TABLE TB_ALBUM(
  id NUMBER NOT NULL,
  title VARCHAR2(40) NOT NULL,
  album_year NUMBER,
  id_artist NUMBER NOT NULL,
  id_recorder NUMBER NOT NULL,
  id_updater NUMBER NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT ALBUM_id_pk PRIMARY KEY (id)
);


ALTER TABLE TB_ALBUM ADD CONSTRAINT ALBUM_id_artist_fk FOREIGN KEY (id_artist) REFERENCES TB_ARTIST (id);
ALTER TABLE TB_ALBUM ADD CONSTRAINT ALBUM_id_recorder_fk FOREIGN KEY (id_recorder) REFERENCES TB_RECORDER (id);
ALTER TABLE TB_ALBUM ADD CONSTRAINT ALBUM_id_updater_fk FOREIGN KEY (id_updater) REFERENCES TB_USER (id);

CREATE TABLE TB_SONG(
  id NUMBER NOT NULL,
  title VARCHAR2(50) NOT NULL,
  duration NUMBER NOT NULL,
  lyrics VARCHAR2(4000) NOT NULL,
  lyrics_translation VARCHAR2(4000),
  id_genre NUMBER NOT NULL,
  id_album NUMBER NOT NULL,
  id_updater NUMBER NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT SONG_id_pk PRIMARY KEY (id)
);


ALTER TABLE TB_SONG ADD CONSTRAINT SONG_id_genre_fk FOREIGN KEY (id_genre) REFERENCES TB_GENRE (id);
ALTER TABLE TB_SONG ADD CONSTRAINT SONG_id_album_fk FOREIGN KEY (id_album) REFERENCES TB_ALBUM (id);
ALTER TABLE TB_SONG ADD CONSTRAINT SONG_id_updater_fk FOREIGN KEY (id_updater) REFERENCES TB_USER (id);

CREATE TABLE TB_SONG_COMPOSER(
  id_song NUMBER NOT NULL,
  id_composer NUMBER NOT NULL
);

ALTER TABLE TB_SONG_COMPOSER ADD CONSTRAINT SONGCOMP_id_song_fk FOREIGN KEY (id_song) REFERENCES TB_SONG (id);
ALTER TABLE TB_SONG_COMPOSER ADD CONSTRAINT SONGCOMP_id_composer_fk FOREIGN KEY (id_composer) REFERENCES TB_MUSICIAN (id);

CREATE SEQUENCE SEQALBUM INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQUSER INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQARTIST INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQCITY INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQGENRE INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQMUSICIAN INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQRECORDER INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;
CREATE SEQUENCE SEQSONG INCREMENT BY 1 MAXVALUE 999999999999999 MINVALUE 1 NOCACHE ORDER;


--carga inicial de usuario
INSERT INTO TB_USER  VALUES (SEQUSER.NEXTVAL, 'admin', 'admin', 'admin', 'Y');
COMMIT;